package FillMythMetadataSql;
use FillMythMetadataUtilities;
use strict;
use warnings;

my $dbh;
my $local_machine_hostname;
my ($second, $minute, $hour, $dayOfMonth, $month, $yearOffset, $dayOfWeek, $dayOfYear, $daylightSavings) = localtime();
#2010-08-05 22:28:15
#20 36 8 27 11 105
my $year = 1900+$yearOffset;
my $time_string =  "$year-$month-$dayOfMonth $hour:$minute:$second";

sub set_db_vars
{
	$dbh = $_[0];
	$local_machine_hostname = $_[1];
	
	my $sqlstat=qq {select data from settings where value = 'DBSchemaVer'};
	my $sth = $dbh->prepare($sqlstat);
	$sth->execute();
	#I'm doing the query, and defining the variable in the global scoop, so we'll have this
	#easily accessable for any future needs.
	my $mythtvDBSchemaVersion;
	$sth->bind_columns( undef, \$mythtvDBSchemaVersion );

	$sth->fetch();
	print "Found DB schema version: $mythtvDBSchemaVersion.\n";

	if ($mythtvDBSchemaVersion < 1254)
	{
		print "DB schema is before Myth TV 0.23. Can not run!.\n";
		die;
	}
}

sub get_videos_folders
{
	my @folders = ();
	#local videos
#	my $sqlstat=qq {select data from settings where value="VideoStartupDir" and hostname="$local_machine_hostname"};
#	my $sth = $dbh->prepare($sqlstat);
#	$sth->execute();
#	my $mythvideo_video_dir;
#	$sth->bind_columns( undef, \$mythvideo_video_dir );
#	while($sth->fetch())
#	{
#		my $video_folder = FillMythMetadataUtilities::remove_last_sep($mythvideo_video_dir);
#		print "Local video folder: $video_folder\n";
#		push(@folders, $video_folder);
#	}

	#storage groups
	my $sqlstat=qq {select dirname from storagegroup where groupname="Videos" and hostname="$local_machine_hostname"};
	my $sth = $dbh->prepare($sqlstat);
	$sth->execute();
	my $mythvideo_video_dir;
	$sth->bind_columns( undef, \$mythvideo_video_dir );
	while($sth->fetch())
	{
			my $video_folder = FillMythMetadataUtilities::remove_last_sep($mythvideo_video_dir);
			print "Storage video folder: $video_folder\n";
			push(@folders, $video_folder);
	}

	return @folders;
}

sub get_banners_folder
{
	my $sqlstat=qq {select dirname from storagegroup where groupname="Banners" and hostname="$local_machine_hostname"};
	my $sth = $dbh->prepare($sqlstat);
	$sth->execute();
	my $mythvideo_dir;
	$sth->bind_columns( undef, \$mythvideo_dir );
	while($sth->fetch())
	{
			my $folder = FillMythMetadataUtilities::remove_last_sep($mythvideo_dir);
			return $folder;
	}
	return "";
}

sub get_fanart_folder
{
	my $sqlstat=qq {select dirname from storagegroup where groupname="Fanart" and hostname="$local_machine_hostname"};
	my $sth = $dbh->prepare($sqlstat);
	$sth->execute();
	my $mythvideo_dir;
	$sth->bind_columns( undef, \$mythvideo_dir );
	while($sth->fetch())
	{
			my $folder = FillMythMetadataUtilities::remove_last_sep($mythvideo_dir);
			return $folder;
	}
	return "";
}

sub get_screenshots_folder
{
	my $sqlstat=qq {select dirname from storagegroup where groupname="Screenshots" and hostname="$local_machine_hostname"};
	my $sth = $dbh->prepare($sqlstat);
	$sth->execute();
	my $mythvideo_dir;
	$sth->bind_columns( undef, \$mythvideo_dir );
	while($sth->fetch())
	{
			my $folder = FillMythMetadataUtilities::remove_last_sep($mythvideo_dir);
			return $folder;
	}
	return "";
}

sub get_coverart_folder
{
	my $sqlstat=qq {select dirname from storagegroup where groupname="Coverart" and hostname="$local_machine_hostname"};
	my $sth = $dbh->prepare($sqlstat);
	$sth->execute();
	my $mythvideo_dir;
	$sth->bind_columns( undef, \$mythvideo_dir );
	while($sth->fetch())
	{
			my $folder = FillMythMetadataUtilities::remove_last_sep($mythvideo_dir);
			return $folder;
	}
	return "";
}

sub get_video_exts
{
	my $sqlstat=qq {select extension from videotypes where f_ignore=0};
	my $sth = $dbh->prepare($sqlstat);
	$sth->execute();
	my $a_video_ext;
	$sth->bind_columns( undef, \$a_video_ext );
	$sth->fetch();
	my $video_exts = $a_video_ext;
	while($sth->fetch())
	{
		$video_exts = $video_exts."|".$a_video_ext;
	}
	return $video_exts;
}

sub insert_dummy_row
{
	my $sql_fullfilename = ensure_string_is_valid_for_SQL($_[0]);
	my $sql_filename = ensure_string_is_valid_for_SQL($_[1]);
	my $default_show_level = $_[2];
	my $video_length = $_[3];
	my $hash = $_[4];
	
	my $sqlstat=qq {insert videometadata (title,rating,showlevel,browse,filename,host,subtitle,director,plot,childid,insertdate,inetref,hash,length) 
		values 
		("$sql_filename","NR", "$default_show_level", 1, "$sql_fullfilename", "$local_machine_hostname", "", "Unknown","None",-1,"$time_string", "empty", "$hash", $video_length)};
	my $sth = $dbh->prepare($sqlstat);
	$sth->execute();
}

sub ensure_string_is_valid_for_SQL
{
	my $text = $_[0];
	if (!$text) 
	{
		return "";
	}
	else
	{
		#http://www.theukwebdesigncompany.com/articles/entity-escape-characters.php
		#issue36: sometimes, the string is already fixed!
		$text =~ s/\\?\"/\\"/gi;
		#html escape codes
		$text =~ s/&amp;/&/gi;
		$text =~ s/&nbsp;/ /gi;
		$text =~ s/&quot;/\\"/gi;
		$text =~ s/&apos;/\\'/gi;
		$text =~ s/&lt;/</gi;
		$text =~ s/&gt;/>/gi;
		#issue 57. Now we suppose to support HTML escape codes.
		$text =~ s/(\&\#\d{2}\;)/chr(substr($1, 2, 2))/eg;
		$text =~ s/(\&\#x[a-fA-F0-9]{2}\;)/chr(hex(substr($1, 3, 2)))/eg;
		#if I missed something.
		$text =~ s/&#\d{1,8}?;/ /gi;
		$text =~ s/&#x\d{1,8}?;/ /gi;
		return $text;
	}
}

sub get_inetref_for_file
{
	my $fullfilename = $_[0];
	my $hash = $_[1];

	my $sql_fullfilename = ensure_string_is_valid_for_SQL($fullfilename);
	my $sqlstat=qq {select inetref, filename, hash from videometadata where hash="$hash" OR filename="$sql_fullfilename"};
	my $sth = $dbh->prepare($sqlstat);
	$sth->execute();
	my ($current_show_ref, $current_filename, $current_hash);
	$sth->bind_columns( undef, \$current_show_ref, \$current_filename, \$current_hash);
	if ($sth->fetch())
	{
		#fixing sql row (maybe file has moved?
		if (!($fullfilename eq $current_filename))
		{
			#means the file has moved.
			print "It seems $fullfilename has moved. Re-setting DB path...\n";
			$sqlstat =
                	qq {
                		update videometadata
               			set
                		filename="$sql_fullfilename"
                		where hash="$hash"
                	};
        		$sth = $dbh->prepare($sqlstat);
        		$sth->execute();
		}
		elsif (!($hash eq $current_hash))
		{
			#wow, cool, the hash code of the file has changed. Could it mean that file scan was of a partial file (bit-torrent?)?
			print "It seems $fullfilename has a different hash signature. Will assume the file has changed, and requires re-setting.\n";
                        return "empty"
		}
		
		return $current_show_ref;
	}
	else
	{
		return undef;
	}
}

sub is_cover_image_valid
{
	my $fullfilename = $_[0];
	my $sql_fullfilename = ensure_string_is_valid_for_SQL($fullfilename);
	my $sqlstat=qq {select coverfile from videometadata where filename="$sql_fullfilename"};
	my $sth = $dbh->prepare($sqlstat);
	$sth->execute();

	my $coverfile;
	$sth->bind_columns( undef, \$coverfile );
	my $invalid_cover_file = 0;
	if (!$sth->fetch())
	{
		print "Could not locate cover file in the database.\n";
		$invalid_cover_file = 1;
	}
	elsif ((!$coverfile) || (length $coverfile eq 0) || (!-e $coverfile) || (-s $coverfile < 100) || ($coverfile =~ m/folder\.\w{3}$/))#empty or ends with folder.*
	{
		print "Invalid cover file '$coverfile'!\n";
		$invalid_cover_file = 1;
	}
	
	if ($invalid_cover_file eq 1)
	{
		#print "The cover of the video file is invalid! I'll try to regenerate it...\n";
		return 0;
	}
	else
	{
		return 1;
	}
}

sub update_file_date
{
	my $sql_fullfilename = FillMythMetadataSql::ensure_string_is_valid_for_SQL($_[0]);
	my $time_string = $_[1];
	
	my $sqlstat =
		qq {
		update videometadata
		set
		insertdate="$time_string"
		where filename="$sql_fullfilename"
		};
	my $sth = $dbh->prepare($sqlstat);
	$sth->execute();
}

sub update_simple_metadata
{
	my $sql_filename = ensure_string_is_valid_for_SQL($_[0]);
	my $sql_path = ensure_string_is_valid_for_SQL($_[1]);
	my $sql_fullfilename = ensure_string_is_valid_for_SQL($_[2]);
	my $video_length = $_[3];
	my $hash = $_[4];
	
	my $sqlstat=
		qq {
		update videometadata
		set
		title="$sql_filename", plot="", director="", year=0, inetref="simple",
		coverfile="$sql_path", length=$video_length, userrating=0, hash="$hash"
		where filename="$sql_fullfilename"
		};
	my $sth = $dbh->prepare($sqlstat);
	$sth->execute();
}

sub update_unknown_video
{
	my $video_hash = $_[0];
        my $sql_title = FillMythMetadataSql::ensure_string_is_valid_for_SQL($_[1]);
        my $sql_image_path= FillMythMetadataSql::ensure_string_is_valid_for_SQL($_[2]);

	 my $sqlstat = qq {
                update videometadata
                set
                title="$sql_title", subtitle="$sql_title",
                coverfile="$sql_image_path", screenshot="$sql_image_path", banner="$sql_image_path", fanart="$sql_image_path"
                where hash="$video_hash"
                };

        my $sth = $dbh->prepare($sqlstat);
        $sth->execute();
}

#fullfilename
#inetref
#title 
#subtitle
#season*
#episode*
#plot
#video_length
#user_rating*
#directors
#year*
#content_rating
#screenshot_path
#coverfile_path
#banner_path
#fanart_path
#hash
sub update_video_metadata
{
	my $sql_fullfilename = FillMythMetadataSql::ensure_string_is_valid_for_SQL($_[0]);
	my $sql_episode_url_for_inetref = FillMythMetadataSql::ensure_string_is_valid_for_SQL($_[1]);
	
	my $sql_title = FillMythMetadataSql::ensure_string_is_valid_for_SQL($_[2]);
	my $sql_subtitle = FillMythMetadataSql::ensure_string_is_valid_for_SQL($_[3]);
	my $season = $_[4];
	my $episode = $_[5];
	
	my $sql_plot = FillMythMetadataSql::ensure_string_is_valid_for_SQL($_[6]);
	my $video_length = $_[7];
	my $user_rating = $_[8];
	
	my $sql_directors = FillMythMetadataSql::ensure_string_is_valid_for_SQL($_[9]);
	my $year = $_[10];
	
	my $sql_content_rating = FillMythMetadataSql::ensure_string_is_valid_for_SQL($_[11]);
	my $showlevel = FillMythMetadataParentalRating::parse_show_level($_[11]);
	
	my $sql_screenshot_path = FillMythMetadataSql::ensure_string_is_valid_for_SQL($_[12]);
	my $sql_coverfile_path = FillMythMetadataSql::ensure_string_is_valid_for_SQL($_[13]);
	my $sql_banner_path = FillMythMetadataSql::ensure_string_is_valid_for_SQL($_[14]);
	my $sql_fanart_path = FillMythMetadataSql::ensure_string_is_valid_for_SQL($_[15]);
	
	my $hash = $_[16];
	
	my $sqlstat = qq {
		update videometadata
		set
		title="$sql_title", subtitle="$sql_subtitle", plot="$sql_plot", director="$sql_directors", year=$year, inetref="$sql_episode_url_for_inetref",
		coverfile="$sql_coverfile_path", length=$video_length, userrating=$user_rating, rating="$sql_content_rating", season=$season, episode=$episode,
		screenshot="$sql_screenshot_path", banner="$sql_banner_path", fanart="$sql_fanart_path", showlevel=$showlevel, host="$local_machine_hostname",
		watched=0, homepage="$sql_episode_url_for_inetref", browse=1, hash="$hash",
		filename="$sql_fullfilename"
		where hash="$hash"
		};

	#print "sql: ".$sqlstat."\n";
													
	my $sth = $dbh->prepare($sqlstat);
	$sth->execute();
}

1;
